﻿<cfscript>
/** 
* graduateFactory
* 
* @hint "Graduate Audit Factory" 
*/ 

component 
	displayname="public.service.handler.graduateFactory" 
	output=true 
	accessors=true
	{

	/*PROPERTIES*/
	property name="datetimeAdvice" hint="日期时间操作组件";
	property name="markAdvice" hint="成绩操作组件";
	property name="sequenceGraduateDetail" hint="审核明细ID生成组件";
	property name="sessionAdvice" hint="会话组件";
	property name="sequenceMarkNeed" hint="课程记录ID生成器";

	/**
	* @hint "初始化对象"
	*/
	public public.service.handler.graduateFactory function init(){
		return this;
	}
	
	/**
	* @hint "初始化条件数据"
	* 
	* @templateId "审核条件"
	* @studentId "学生学号"
	* @params "审核参数"
	*/
	public void function initAudit( required string templateId, required string studentId, any params ) output=true {
		
		var sql = "";
		var queryObj = "";
		
		var sequence = getsequenceGraduateDetail();
		
		/* Every SQL operation is TRANSACATION*/
		transaction {
			
			/* 当前日期时间字符串 */
			var currentDateTime = getdatetimeAdvice().getCurrentDatetimeString();
			
			/* 当前审阅人 */
			var teacherId = getsessionAdvice().getAutherUserID();
			
			
			/* 生成数据主键 */
			sequence.setStudentID( studentId );
			sequence.setGraduateCondition( templateId );
			
			var detailId = sequence.getID();

			/* 检查该数据是否存在 */
			sql = "SELECT 1 FROM t_grad_details WHERE stu_gdr = :detailId";
			
			queryObj = new Query( datasource=application.dnsMaster );
			queryObj.addParam( name="detailId", value=detailId, cfsqltype="cf_sql_varchar" );
	
			var rs_check = queryObj.execute( sql=sql ).getResult();
			
			if ( rs_check.recordCount eq 0 ) {
				
				/* 如果该数据不存在 则直接生成一个默认值记录 */
				sql = "INSERT INTO t_grad_details 
						( stu_gdr, stu_id, gdc, gdc_params, is_passed, result_descript, tch_id, date_created ) 
						VALUES 
						( :detailId, :studentId, :templateId, :params, '-', 'NOPE', :teacherId, :currentDateTime )";
				
				queryObj = new Query( datasource=application.dnsMaster );
				queryObj.addParam( name="detailId", value=detailId, cfsqltype="cf_sql_varchar" );
				queryObj.addParam( name="studentId", value=studentId, cfsqltype="cf_sql_varchar" );
				queryObj.addParam( name="templateId", value=templateId, cfsqltype="cf_sql_char" );
				queryObj.addParam( name="params", value=params, cfsqltype="cf_sql_varchar" );
				queryObj.addParam( name="teacherId", value=teacherId, cfsqltype="cf_sql_varchar" );
				queryObj.addParam( name="currentDateTime", value=currentDateTime, cfsqltype="cf_sql_char" );
				
				queryObj.execute( sql=sql );

			}
			
		}
		
	}
	

	/**
	* @hint "按条件审核"
	* 
	* @templateId "审核条件"
	* @studentId "学生学号"
	* @params "审核参数"
	*/
	public void function audit( required string templateId, required string studentId, any params ) output=true {
		
		switch(templateId){

			case "CPR":		//完成所有必修课
				auditCourseRequired( templateId,studentId );
				break;

			case "CBR":		//完成所有除最后一学位之外的必修课
				auditCourseRequiredExcludeLastTerm( templateId,studentId );
				break;

			case "GPR":		//完成必修课平均绩点要求
				auditCourseRequiredAvgCreditPoint( templateId,studentId,params );
				break;
				
			case "GPA":		//总学分平均绩点
				auditCourseCreditPoint( templateId,studentId,params );
				break;

			case "CRP":		//完成必修课学分要求
				auditCourseRequiredCredit( templateId,studentId,params );
				break;

			case "CRA":		//完成课程总学分要求
				auditCourseCredit( templateId,studentId,params );
				break;
			
			case "CRT":		//体育课学分要求
				auditCoursePhyCredit( templateId,studentId,params );
				break;
				
			case "CRC":		//素质选修课学分要求
				auditCourseOptionalCredit( templateId,studentId,params );
				break;

		}
		
	}

	/**
	* @hint "更新审核结论"
	* 
	* @studentId "学生学号"
	*/
	public string function auditPass( required string studentId ) output=true {
		
		/* 学生最终是否通过机审 */
		var passed = "0";
		
		/* 当前日期时间字符串 */
		var currentDateTime = getdatetimeAdvice().getCurrentDatetimeString();
			
		/* 当前审阅人 */
		var teacherId = getsessionAdvice().getAutherUserID();
		
		/* 学生的所有审核条件完成后, 判断机审条件是否都通过 */
		var sql = "SELECT is_passed FROM t_grad_details WHERE stu_id = :studentId";
		
		var queryObj = new Query( datasource=application.dnsSlave );
		
		queryObj.addParam( name="studentId", value=studentId, cfsqltype="cf_sql_varchar" );
		
		var rs_detail = queryObj.execute( sql=sql ).getResult();
		
		
		var gdcPassed = 0;
		var gdcFailure = 0;
		
		for (var a=1; a LTE rs_detail.recordCount; a++ ) {
			
			if ( rs_detail["is_passed"][a] eq "1" ) {
				gdcPassed++;
			}
			else {
				gdcFailure++;
			}
			
		}

		if ( gdcFailure eq 0 ) {
			passed = "1";
		} else {
			passed = "0";
		}

		/* 更新学生的机审结果 */
		
		/* Every SQL operation is TRANSACATION*/
		transaction {
		
			sql = "UPDATE t_grad_review 
					SET 
						comp_passed = :passed, 
						gdc_passed = :gdcPassed, 
						gdc_failure = :gdcFailure, 
						date_review = :currentDateTime, 
						tch_id = :teacherId 
					WHERE 
						stu_id = :studentId ";

			queryObj = new Query( datasource=application.dnsMaster );

			queryObj.addParam( name="studentId", value=studentId, cfsqltype="cf_sql_varchar" );
			queryObj.addParam( name="passed", value=passed, cfsqltype="cf_sql_char" );
			queryObj.addParam( name="gdcPassed", value=gdcPassed, cfsqltype="cf_sql_integer" );
			queryObj.addParam( name="gdcFailure", value=gdcFailure, cfsqltype="cf_sql_integer" );
			queryObj.addParam( name="teacherId", value=teacherId, cfsqltype="cf_sql_varchar" );
			queryObj.addParam( name="currentDateTime", value=currentDateTime, cfsqltype="cf_sql_char" );

			queryObj.execute( sql=sql );
		
		}
		
		return passed;
		
	}

	/**
	*
	* @hint "审核学生的必修课是否通过并记录"
	* 
	* @templateId "模板"
	* @studentId "学号"
	*/
	public void function auditCourseRequired( required string templateId,required string studentId ) output=true {
		
		transaction{
		
			/*成绩组件*/
			var markAdvice = getmarkAdvice();
			var sequenceGraduateDetail = getsequenceGraduateDetail();
			var sequenceMarkNeed = getsequenceMarkNeed();
			
			sequenceGraduateDetail.setStudentID(studentId);
			sequenceGraduateDetail.setGraduateCondition(templateId);
			var detailId = sequenceGraduateDetail.getID();
			
			/* 当前日期时间字符串 */
			var currentDateTime = getdatetimeAdvice().getCurrentDatetimeString();
			
			/* 当前审阅人 */
			var teacherId = getsessionAdvice().getAutherUserID();
			
			/*查询学生的修读专业信息*/
			var sql = "	SELECT
							stu.stu_id, clz.grade, sbj.sbj_id, stu.sbj_direction
						FROM
							t_student stu INNER JOIN t_class clz ON stu.cls_id = clz.cls_id INNER JOIN t_subject sbj ON clz.sbj_id = sbj.sbj_id
						WHERE
							stu.stu_id = :studentId ";
	
			var queryObj = new Query( datasource=application.dnsMaster );
	
			queryObj.addParam( name="studentId", value=studentId, cfsqltype="cf_sql_varchar" );
	
			var rs_student_subject = queryObj.execute( sql=sql ).getResult();
	
			/*学生需要审核的课程*/
			sql = "	SELECT s.cid
						FROM t_schema s 
						WHERE 
							s.grade = :grade 
							AND 
							( s.sbj_id = :subjectId OR s.sbj_id = :subjectDir ) 
							AND 
							s.prop_id = '1' ";
	
			queryObj = new Query( datasource=application.dnsMaster );
	
			queryObj.addParam( name="grade", value=rs_student_subject.grade, cfsqltype="cf_sql_char" );
			queryObj.addParam( name="subjectId", value=rs_student_subject.sbj_id, cfsqltype="cf_sql_varchar" );
			queryObj.addParam( name="subjectDir", value=rs_student_subject.sbj_id & rs_student_subject.sbj_direction, cfsqltype="cf_sql_varchar" );
	
			var rs_need_course = queryObj.execute( sql=sql ).getResult();
			
			var needCourse = [];
			
			for(var i = 1; i le rs_need_course.recordCount; i++){
				arrayAppend(needCourse, rs_need_course["cid"][i]);
			}
			
			/*学生通过成绩，包括成绩替代*/
			sql = "	SELECT a.cid 
						FROM t_student_mark a 
						WHERE 
							a.stu_id = :studentId 
							AND 
							( 
								a.credit_point > 0 
								OR 
								EXISTS (SELECT 1 FROM t_mark_replace b WHERE a.scm_id = b.scm_id) 
							)";
	
			queryObj = new Query( datasource=application.dnsMaster );
	
			queryObj.addParam( name="studentId", value=studentId, cfsqltype="cf_sql_varchar" );
	
			var rs_done_course = queryObj.execute( sql=sql ).getResult();
			
			var doneCourse = [];
			
			/*构建已经通过的过程*/
			for( var i=1; i le rs_done_course.recordCount; i++){
				arrayAppend(doneCourse, rs_done_course["cid"][i]);
			}
	
			/*直接删除已经通过的课程*/
			for(var i=1; i le arrayLen(doneCourse); i++ ){
				if( arrayFind( needCourse, doneCourse[i] ) ){
					arrayDelete(needCourse, doneCourse[i]);
				}
			}
	
			/*处理课程替代*/
			if( arrayLen(needCourse) ){
				
				requiredCourse = [];
				
				for(var i=1; i le arrayLen(needCourse); i++){
					arrayAppend( requiredCourse, needCourse[i] );
				}
				
				for(var i=1; i le arrayLen(requiredCourse); i++){
					
					needCourseID = requiredCourse[i];
					
					/*全局成绩替代*/
					sql = "	SELECT
								a.cid_b
							FROM
								t_course_replace a
							WHERE
								a.cid_a = :courseId ";
	
					queryObj = new Query( datasource=application.dnsMaster );
					
					queryObj.addParam( name="courseId", value=needCourseID, cfsqltype="cf_sql_varchar" );
					
					var rs_replaceCourse = queryObj.execute( sql=sql ).getResult();
	
					for( var j=1; j le rs_replaceCourse.recordCount; j++ ){
						if(arrayFind( doneCourse, rs_replaceCourse["cid_b"][j] )){
							arrayDelete( needCourse, needCourseID );
							break;
						}
					}
	
					/*如果已经删除，直接处理下一个元素*/
					if( not arrayFind(needCourse,needCourseID) ){
						continue;
					}
					
					/*具体专业年级课程替代*/
					sql = "	SELECT
								r.cid_b
							FROM
								t_schema_course_replace r
							WHERE
								r.grade = :grade 
								AND
								r.sbj_id = :subjectId 
								AND
								r.cid_a = :courseId ";
	
					queryObj = new Query( datasource=application.dnsMaster );
					
					queryObj.addParam( name="courseId", value=needCourseID, cfsqltype="cf_sql_varchar" );
					queryObj.addParam( name="subjectId", value=rs_student_subject.sbj_id, cfsqltype="cf_sql_varchar" );
					queryObj.addParam( name="grade", value=rs_student_subject.grade, cfsqltype="cf_sql_char" );
	
					var rs_replaceCourse = queryObj.execute( sql=sql ).getResult();
	
					for( var j=1; j le rs_replaceCourse.recordCount; j++ ){
						if(arrayFind( doneCourse, rs_replaceCourse["cid_b"][j] )){
							arrayDelete( needCourse, needCourseID );
							break;
						}
					}
					
					/*如果已经删除，直接处理下一个元素*/
					if( not arrayFind(needCourse,needCourseID) ){
						continue;
					}
					
					/* 学生课程成绩替代 */
					sql = "	SELECT
								t.stu_id,t.cid_a,t.cid_b
							FROM
								t_student_course_replace t
							WHERE
								t.stu_id = :studentId 
								AND
								t.cid_a = :courseId ";
	
					queryObj = new Query( datasource=application.dnsMaster );
					
					queryObj.addParam( name="courseId", value=needCourseID, cfsqltype="cf_sql_varchar" );
					queryObj.addParam( name="studentId", value=studentId, cfsqltype="cf_sql_varchar" );
	
					var rs_replaceCourse = queryObj.execute( sql=sql ).getResult();
	
					for( var j=1; j le rs_replaceCourse.recordCount; j++ ){
						if(arrayFind( doneCourse, rs_replaceCourse["cid_b"][j] )){
							arrayDelete( needCourse, needCourseID );
							break;
						}
					}
					
				}
			}
			
			/*删除未通过课程记录*/
			sql = "	DELETE FROM
						t_mark_need n
					WHERE
						n.stu_id = :studentId 
						AND
						n.gdc = :templateId ";
			
			queryObj = new Query( datasource = application.dnsMaster );
			
			queryObj.addParam( name="studentId", value=studentId, cfsqltype="cf_sql_varchar" );
			queryObj.addParam( name="templateId", value=templateId, cfsqltype="cf_sql_char" );
			
			queryObj.execute(sql=sql);
			
			/*最终判断是否还有成绩未通过*/
			if( arrayLen(needCourse) ){
				
				for( a=1; a lte arrayLen(needCourse); a++ ){
					
					sql = "	INSERT INTO t_mark_need 
								( 
									stu_gdcrc, stu_id, gdc, cid 
								) 
								VALUES 
								( 
									:id, :studentId, :templateId, :courseId 
								)";
					
					sequenceMarkNeed.setStudentId(studentId);
					sequenceMarkNeed.setgraduateCondition(templateId);
					sequenceMarkNeed.setcourseId(needCourse[a]);
					id = sequenceMarkNeed.getID();
					
					queryObj = new Query( datasource = application.dnsMaster );
			
					queryObj.addParam( name="id", value=id, cfsqltype="cf_sql_varchar" );
					queryObj.addParam( name="studentId", value=studentId, cfsqltype="cf_sql_char" );
					queryObj.addParam( name="templateId", value=templateId, cfsqltype="cf_sql_varchar" );
					queryObj.addParam( name="courseId", value=needCourse[a], cfsqltype="cf_sql_char" );
					
					queryObj.execute(sql=sql);
				}
				
				sql = "	SELECT
							c.cid,c.course_code, c.course_name
						FROM
							t_course c
						WHERE
							c.cid IN ( :courseIds  )";
				
				queryObj = new Query(datasource=application.dnsMaster);
				
				queryObj.addParam( name="courseIds", value=arrayToList(needCourse,","), cfsqltype="cf_sql_varchar", list=true );
				
				rs_unpass_course = queryObj.execute( sql=sql ).getResult();
				
				resultDesc = "";
				
				for( var i=1; i le rs_unpass_course.recordCount; i++ ){
					
					if( i gt 4 ) {
						if(rs_unpass_course.recordCount gt 4){
							resultDesc = resultDesc & "...";	
						}
						break;
					}
					
					if( i gt 1 ) {
						resultDesc = resultDesc & ",";
					}
					
					resultDesc = resultDesc &  "[" & rs_unpass_course["course_code"][i] & "]" & rs_unpass_course["course_name"][i];
					
				}
	
				resultDesc = resultDesc & " 等" & rs_unpass_course.recordCount & "门课程未通过审核!";
				passed = '0';
				
				sql = "	UPDATE
						t_grad_details g
					SET
						g.is_passed = :passed,
						g.result_descript = :resultDesc,
						g.tch_id = :teacherId,
						g.date_created = :currentDateTime 
					WHERE
						g.stu_gdr = :detailId ";
	
				queryObj = new Query( datasource=application.dnsMaster );
	
				queryObj.addParam( name="passed", value= passed, cfsqltype="cf_sql_char");
				queryObj.addParam( name="resultDesc", value=resultDesc, cfsqltype="cf_sql_varchar");
				queryObj.addParam( name="detailId", value=detailId, cfsqltype="cf_sql_varchar");
				queryObj.addParam( name="teacherId", value=teacherId, cfsqltype="cf_sql_varchar" );
				queryObj.addParam( name="currentDateTime", value=currentDateTime, cfsqltype="cf_sql_char" );
			
				queryObj.execute( sql=sql );
				
			}else{
				
				resultDesc = "已经完成所有培养方案的必修课课程";
				passed = '1';
				
				sql = "	UPDATE
						t_grad_details g
					SET
						g.is_passed = :passed,
						g.result_descript = :resultDesc,
						g.tch_id = :teacherId,
						g.date_created = :currentDateTime 
					WHERE
						g.stu_gdr = :detailId ";
	
				queryObj = new Query( datasource=application.dnsMaster );
	
				queryObj.addParam( name="passed", value= passed, cfsqltype="cf_sql_char");
				queryObj.addParam( name="resultDesc", value=resultDesc, cfsqltype="cf_sql_varchar");
				queryObj.addParam( name="detailId", value=detailId, cfsqltype="cf_sql_varchar");
				queryObj.addParam( name="teacherId", value=teacherId, cfsqltype="cf_sql_varchar" );
				queryObj.addParam( name="currentDateTime", value=currentDateTime, cfsqltype="cf_sql_char" );
			
				queryObj.execute( sql=sql );
				
			}

		}
	}


	/**
	*
	* @hint "审核学生除最后一学期的必修课是否通过并记录"
	* 
	* @templateId "模板"
	* @studentId "学号"
	*/
	public void function auditCourseRequiredExcludeLastTerm( required string templateId,required string studentId ) output=true {
		
		transaction{
		
			/*成绩组件*/
			var markAdvice = getmarkAdvice();
			var sequenceGraduateDetail = getsequenceGraduateDetail();
			var sequenceMarkNeed = getsequenceMarkNeed();
			
			sequenceGraduateDetail.setStudentID(studentId);
			sequenceGraduateDetail.setGraduateCondition(templateId);
			var detailId = sequenceGraduateDetail.getID();
			
			/* 当前日期时间字符串 */
			var currentDateTime = getdatetimeAdvice().getCurrentDatetimeString();
			
			/* 当前审阅人 */
			var teacherId = getsessionAdvice().getAutherUserID();
			
			/*查询学生的修读专业信息*/
			var sql = "	SELECT
							stu.stu_id, clz.grade, sbj.sbj_id, stu.sbj_direction
						FROM
							t_student stu INNER JOIN t_class clz ON stu.cls_id = clz.cls_id INNER JOIN t_subject sbj ON clz.sbj_id = sbj.sbj_id
						WHERE
							stu.stu_id = :studentId ";
	
			var queryObj = new Query( datasource=application.dnsMaster );
	
			queryObj.addParam( name="studentId", value=studentId, cfsqltype="cf_sql_varchar" );
	
			var rs_student_subject = queryObj.execute( sql=sql ).getResult();
	
			/*学生需要审核的课程*/
			sql = "	SELECT s.cid
						FROM t_schema s
						WHERE 
							s.grade = :grade 
							AND 
							( s.sbj_id = :subjectId OR s.sbj_id = :subjectDir ) 
							AND 
							s.prop_id = '1' 
							AND 
							s.term_index != ( 
								SELECT MAX(t.term_index) max_term_index 
									FROM t_schema t 
									WHERE 
										t.grade = :grade 
										AND 
										( t.sbj_id = :subjectId OR t.sbj_id = :subjectDir ) 
										AND 
										t.prop_id = '1' 
										AND 
										( t.term_index NOT LIKE 'S_' ) 
										)";

			queryObj = new Query( datasource=application.dnsMaster );
	
			queryObj.addParam( name="grade", value=rs_student_subject.grade, cfsqltype="cf_sql_char" );
			queryObj.addParam( name="subjectId", value=rs_student_subject.sbj_id, cfsqltype="cf_sql_varchar" );
			queryObj.addParam( name="subjectDir", value=rs_student_subject.sbj_id & rs_student_subject.sbj_direction, cfsqltype="cf_sql_varchar" );
	
			var rs_need_course = queryObj.execute( sql=sql ).getResult();
			
			var needCourse = [];
			
			for(var i = 1; i le rs_need_course.recordCount; i++){
				arrayAppend(needCourse, rs_need_course["cid"][i]);
			}
			
			/*学生通过成绩，包括成绩替代*/
			sql = "	SELECT
						a.cid
					FROM
						t_student_mark a
					WHERE
						a.stu_id = :studentId 
						AND
						(
							a.credit_point > 0 
							OR
							EXISTS (SELECT 1 FROM t_mark_replace b WHERE a.scm_id = b.scm_id)
						)";
	
			queryObj = new Query( datasource=application.dnsMaster );
	
			queryObj.addParam( name="studentId", value=studentId, cfsqltype="cf_sql_varchar" );
	
			var rs_done_course = queryObj.execute( sql=sql ).getResult();
			
			var doneCourse = [];
			
			/*构建已经通过的过程*/
			for( var i=1; i le rs_done_course.recordCount; i++){
				arrayAppend(doneCourse, rs_done_course["cid"][i]);
			}
	
			/*直接删除已经通过的课程*/
			for(var i=1; i le arrayLen(doneCourse); i++ ){
				if( arrayFind( needCourse, doneCourse[i] ) ){
					arrayDelete(needCourse, doneCourse[i]);
				}
			}
	
			/*处理课程替代*/
			if( arrayLen(needCourse) ){
				
				requiredCourse = [];
				
				for(var i=1; i le arrayLen(needCourse); i++){
					arrayAppend( requiredCourse, needCourse[i] );
				}
				
				for(var i=1; i le arrayLen(requiredCourse); i++){
					
					needCourseID = requiredCourse[i];
					
					/*全局成绩替代*/
					sql = "	SELECT
								a.cid_b
							FROM
								t_course_replace a
							WHERE
								a.cid_a = :courseId ";
	
					queryObj = new Query( datasource=application.dnsMaster );
					
					queryObj.addParam( name="courseId", value=needCourseID, cfsqltype="cf_sql_varchar" );
					
					var rs_replaceCourse = queryObj.execute( sql=sql ).getResult();
	
					for( var j=1; j le rs_replaceCourse.recordCount; j++ ){
						if(arrayFind( doneCourse, rs_replaceCourse["cid_b"][j] )){
							arrayDelete( needCourse, needCourseID );
							break;
						}
					}
	
					/*如果已经删除，直接处理下一个元素*/
					if( not arrayFind(needCourse,needCourseID) ){
						continue;
					}
					
					/*具体专业年级课程替代*/
					sql = "	SELECT
								r.cid_b
							FROM
								t_schema_course_replace r
							WHERE
								r.grade = :grade 
								AND
								r.sbj_id = :subjectId 
								AND
								r.cid_a = :courseId ";
	
					queryObj = new Query( datasource=application.dnsMaster );
					
					queryObj.addParam( name="courseId", value=needCourseID, cfsqltype="cf_sql_varchar" );
					queryObj.addParam( name="subjectId", value=rs_student_subject.sbj_id, cfsqltype="cf_sql_varchar" );
					queryObj.addParam( name="grade", value=rs_student_subject.grade, cfsqltype="cf_sql_char" );
	
					var rs_replaceCourse = queryObj.execute( sql=sql ).getResult();
	
					for( var j=1; j le rs_replaceCourse.recordCount; j++ ){
						if(arrayFind( doneCourse, rs_replaceCourse["cid_b"][j] )){
							arrayDelete( needCourse, needCourseID );
							break;
						}
					}
					
					/*如果已经删除，直接处理下一个元素*/
					if( not arrayFind(needCourse,needCourseID) ){
						continue;
					}
					
					/* 学生课程成绩替代 */
					sql = "	SELECT
								t.stu_id,t.cid_a,t.cid_b
							FROM
								t_student_course_replace t
							WHERE
								t.stu_id = :studentId 
								AND
								t.cid_a = :courseId ";
	
					queryObj = new Query( datasource=application.dnsMaster );
					
					queryObj.addParam( name="courseId", value=needCourseID, cfsqltype="cf_sql_varchar" );
					queryObj.addParam( name="studentId", value=studentId, cfsqltype="cf_sql_varchar" );
	
					var rs_replaceCourse = queryObj.execute( sql=sql ).getResult();
	
					for( var j=1; j le rs_replaceCourse.recordCount; j++ ){
						if(arrayFind( doneCourse, rs_replaceCourse["cid_b"][j] )){
							arrayDelete( needCourse, needCourseID );
							break;
						}
					}
					
				}
			}
			
			/*删除未通过课程记录*/
			sql = "	DELETE FROM
						t_mark_need n
					WHERE
						n.stu_id = :studentId 
						AND
						n.gdc = :templateId ";
			
			queryObj = new Query( datasource = application.dnsMaster );
			
			queryObj.addParam( name="studentId", value=studentId, cfsqltype="cf_sql_varchar" );
			queryObj.addParam( name="templateId", value=templateId, cfsqltype="cf_sql_char" );
			
			queryObj.execute(sql=sql);
			
			/*最终判断是否还有成绩未通过*/
			if( arrayLen(needCourse) ){
				
				for( a=1; a lte arrayLen(needCourse); a++ ){
					
					sql = "	INSERT INTO t_mark_need(
								stu_gdcrc,
								stu_id,
								gdc,
								cid
							)VALUES(
								:id,
								:studentId,
								:templateId,
								:courseId 
							)";
					
					sequenceMarkNeed.setStudentId(studentId);
					sequenceMarkNeed.setgraduateCondition(templateId);
					sequenceMarkNeed.setcourseId(needCourse[a]);
					id = sequenceMarkNeed.getID();
					
					queryObj = new Query( datasource = application.dnsMaster );
			
					queryObj.addParam( name="id", value=id, cfsqltype="cf_sql_varchar" );
					queryObj.addParam( name="studentId", value=studentId, cfsqltype="cf_sql_char" );
					queryObj.addParam( name="templateId", value=templateId, cfsqltype="cf_sql_varchar" );
					queryObj.addParam( name="courseId", value=needCourse[a], cfsqltype="cf_sql_char" );
					
					queryObj.execute(sql=sql);
				}
				
				sql = "	SELECT
							c.cid,c.course_code, c.course_name
						FROM
							t_course c
						WHERE
							c.cid IN ( :courseIds  )";
				
				queryObj = new Query(datasource=application.dnsMaster);
				
				queryObj.addParam( name="courseIds", value=arrayToList(needCourse,","), cfsqltype="cf_sql_varchar", list=true );
				
				rs_unpass_course = queryObj.execute( sql=sql ).getResult();
				
				resultDesc = "";
				
				for( var i=1; i le rs_unpass_course.recordCount; i++ ){
					
					if( i gt 4 ) {
						if(rs_unpass_course.recordCount gt 4){
							resultDesc = resultDesc & "...";	
						}
						break;
					}
					
					if( i gt 1 ) {
						resultDesc = resultDesc & ",";
					}
					
					resultDesc = resultDesc &  "[" & rs_unpass_course["course_code"][i] & "]" & rs_unpass_course["course_name"][i];
					
				}
	
				resultDesc = resultDesc & " 等" & rs_unpass_course.recordCount & "门课程未通过审核!";
				passed = '0';
				
				sql = "	UPDATE
						t_grad_details g
					SET
						g.is_passed = :passed,
						g.result_descript = :resultDesc,
						g.tch_id = :teacherId,
						g.date_created = :currentDateTime 
					WHERE
						g.stu_gdr = :detailId ";
	
				queryObj = new Query( datasource=application.dnsMaster );
	
				queryObj.addParam( name="passed", value= passed, cfsqltype="cf_sql_char");
				queryObj.addParam( name="resultDesc", value=resultDesc, cfsqltype="cf_sql_varchar");
				queryObj.addParam( name="detailId", value=detailId, cfsqltype="cf_sql_varchar");
				queryObj.addParam( name="teacherId", value=teacherId, cfsqltype="cf_sql_varchar" );
				queryObj.addParam( name="currentDateTime", value=currentDateTime, cfsqltype="cf_sql_char" );
			
				queryObj.execute( sql=sql );
				
			}else{
				
				resultDesc = "已经完成除最后一学期外所有培养方案的必修课课程";
				passed = '1';
				
				sql = "	UPDATE
						t_grad_details g
					SET
						g.is_passed = :passed,
						g.result_descript = :resultDesc,
						g.tch_id = :teacherId,
						g.date_created = :currentDateTime 
					WHERE
						g.stu_gdr = :detailId ";
	
				queryObj = new Query( datasource=application.dnsMaster );
	
				queryObj.addParam( name="passed", value= passed, cfsqltype="cf_sql_char");
				queryObj.addParam( name="resultDesc", value=resultDesc, cfsqltype="cf_sql_varchar");
				queryObj.addParam( name="detailId", value=detailId, cfsqltype="cf_sql_varchar");
				queryObj.addParam( name="teacherId", value=teacherId, cfsqltype="cf_sql_varchar" );
				queryObj.addParam( name="currentDateTime", value=currentDateTime, cfsqltype="cf_sql_char" );
			
				queryObj.execute( sql=sql );
				
			}

		}
	}


	/**
	*
	* @hint "学生目前总学分 - 大于最低要求"
	* 
	* @templateId "模板"
	* @studentId "学号"
	* @courseCredit "审核学分要求"
	*/
	public void function auditCourseCredit( required string templateId, required string studentId, required numeric courseCredit ) output=true {
		
		var sql = "";
		var queryObj = "";
		
		var sequence = getsequenceGraduateDetail();
		
		var resultDesc = "";
		var passed = "0";
		
		/* Every SQL operation is TRANSACATION*/
		transaction {
			
			/* 当前日期时间字符串 */
			var currentDateTime = getdatetimeAdvice().getCurrentDatetimeString();
			
			/* 当前审阅人 */
			var teacherId = getsessionAdvice().getAutherUserID();
			
			/* 生成数据主键 */
			sequence.setStudentID( studentId );
			sequence.setGraduateCondition( templateId );
			
			var detailId = sequence.getID();
			
			/* 读取学生总学分统计 */
			sql = "SELECT p.credit FROM t_student_profile p WHERE p.stu_id = :studentId ";
	
			queryObj = new Query( datasource=application.dnsMaster );
			queryObj.addParam( name="studentId", value=studentId, cfsqltype="cf_sql_varchar" );
			
			rs_profile = queryObj.execute( sql=sql ).getResult();
			
			if ( rs_profile.credit gte courseCredit ) {
				
				resultDesc = "已达到总学分要求。学生已修读 " & rs_profile.credit & " 学分, 要求修读 " & courseCredit & " 学分";
				passed = "1";
				
			}
			else {
				
				resultDesc = "不满足总学分最低要求。学生已修读 " & rs_profile.credit & " 学分, 要求修读 " & courseCredit & " 学分, 尚有 " & ( courseCredit - rs_profile.credit ) & " 学分需要完成";
				passed = "0";
				
			}
			
			sql = "UPDATE t_grad_details g
					SET
						g.is_passed = :passed,
						g.gdc_params = :params,
						g.tch_id = :teacherId,
						g.date_created = :currentDateTime,
						g.result_descript = :resultDesc 
					WHERE
						g.stu_gdr = :detailId ";
	
			queryObj = new Query( datasource=application.dnsMaster );
	
			queryObj.addParam( name="passed", value=passed, cfsqltype="cf_sql_char");
			queryObj.addParam( name="resultDesc", value=resultDesc, cfsqltype="cf_sql_varchar");
			queryObj.addParam( name="detailId", value=detailId, cfsqltype="cf_sql_varchar");
			queryObj.addParam( name="params", value=courseCredit, cfsqltype="cf_sql_varchar" );
			queryObj.addParam( name="teacherId", value=teacherId, cfsqltype="cf_sql_varchar" );
			queryObj.addParam( name="currentDateTime", value=currentDateTime, cfsqltype="cf_sql_char" );
	
			queryObj.execute( sql=sql );
			
		}
	
	}

	/**
	*
	* @hint "学生目前必修课总学分 - 大于最低要求"
	* 
	* @templateId "模板"
	* @studentId "学号"
	* @courseRequiredCredit "审核学分要求"
	*/
	public void function auditCourseRequiredCredit( required string templateId, required string studentId, required numeric courseRequiredCredit ) output=true {

		var sql = "";
		var queryObj = "";
		
		var sequence = getsequenceGraduateDetail();
		
		var resultDesc = "";
		var passed = "0";
		
		/* Every SQL operation is TRANSACATION*/
		transaction {
			
			/* 当前日期时间字符串 */
			var currentDateTime = getdatetimeAdvice().getCurrentDatetimeString();
			
			/* 当前审阅人 */
			var teacherId = getsessionAdvice().getAutherUserID();
			
			
			/* 生成数据主键 */
			sequence.setStudentID( studentId );
			sequence.setGraduateCondition( templateId );
			
			var detailId = sequence.getID();

			
			/* 读取学生必修课学分统计 */
			sql = "SELECT p.credit_req FROM t_student_profile p WHERE p.stu_id = :studentId ";
	
			queryObj = new Query( datasource=application.dnsMaster );
			queryObj.addParam( name="studentId", value=studentId, cfsqltype="cf_sql_varchar" );
			
			rs_profile = queryObj.execute( sql=sql ).getResult();
			
			
			if ( rs_profile.credit_req gte courseRequiredCredit ) {
				
				resultDesc = "已达到必修课总学分要求。学生已修读必修课 " & rs_profile.credit_req & " 学分, 要求修读 " & courseRequiredCredit & " 学分";
				passed = "1";
				
			}
			else {
				
				resultDesc = "不满足最低必修课学分要求。学生已修读必修课 " & rs_profile.credit_req & " 学分, 要求修读 " & courseRequiredCredit & " 学分, 尚有 " & ( courseRequiredCredit - rs_profile.credit_req ) & " 学分需要完成";
				passed = "0";
				
			}

			sql = "UPDATE t_grad_details g
					SET
						g.is_passed = :passed,
						g.gdc_params = :params,
						g.tch_id = :teacherId,
						g.date_created = :currentDateTime,
						g.result_descript = :resultDesc 
					WHERE
						g.stu_gdr = :detailId ";
	
			queryObj = new Query( datasource=application.dnsMaster );
	
			queryObj.addParam( name="passed", value=passed, cfsqltype="cf_sql_char");
			queryObj.addParam( name="resultDesc", value=resultDesc, cfsqltype="cf_sql_varchar");
			queryObj.addParam( name="detailId", value=detailId, cfsqltype="cf_sql_varchar");
			queryObj.addParam( name="params", value=courseRequiredCredit, cfsqltype="cf_sql_varchar" );
			queryObj.addParam( name="teacherId", value=teacherId, cfsqltype="cf_sql_varchar" );
			queryObj.addParam( name="currentDateTime", value=currentDateTime, cfsqltype="cf_sql_char" );
	
			queryObj.execute( sql=sql );
			
		}
	}
	

	/**
	*
	* @hint "审核学生的体育课学分"
	* 
	* @templateId "模板"
	* @studentId "学号"
	* @courseRequiredCredit "审核学分要求"
	*/
	public void function auditCoursePhyCredit( required string templateId, required string studentId, required numeric coursePhyCredit ) output=true {

		transaction{

			var sequenceGraduateDetail = getsequenceGraduateDetail();
	
			/* 当前日期时间字符串 */
			var currentDateTime = getdatetimeAdvice().getCurrentDatetimeString();
			
			/* 当前审阅人 */
			var teacherId = getsessionAdvice().getAutherUserID();
	
			sequenceGraduateDetail.setStudentID(studentId);
			sequenceGraduateDetail.setGraduateCondition(templateId);
			var detailId = sequenceGraduateDetail.getID();
	
			var sql = "	SELECT
							p.credit_phy
						FROM
							t_student_profile p
						WHERE
							p.stu_id = :studentId ";
	
			var queryObj = new Query( datasource=application.dnsMaster );
			queryObj.addParam( name="studentId", value=studentId, cfsqltype="cf_sql_varchar" );
			var rs_studentProfile = queryObj.execute( sql=sql ).getResult();
	
			var resultDesc = "";
			var passed = "0";
	
			if( rs_studentProfile.credit_phy ge coursePhyCredit){
				resultDesc = "该学生满足体育课总学分要求，学生当前体育课学分：" & rs_studentProfile.credit_phy & ",审核要求学分：" & coursePhyCredit;
				passed = "1";
			}else{
				resultDesc = "该学生不满足体育总学分要求，学生当前体育课学分：" & rs_studentProfile.credit_phy & ",审核要求学分：" & coursePhyCredit;
				passed = "0";
			}
	
			sql = "	UPDATE
						t_grad_details g
					SET
						g.is_passed = :passed,
						g.result_descript = :resultDesc,
						g.tch_id = :teacherId,
						g.date_created = :currentDateTime 
					WHERE
						g.stu_gdr = :detailId ";
	
			queryObj = new Query( datasource=application.dnsMaster );
	
			queryObj.addParam( name="passed", value=passed, cfsqltype="cf_sql_char");
			queryObj.addParam( name="resultDesc", value=resultDesc, cfsqltype="cf_sql_varchar");
			queryObj.addParam( name="detailId", value=detailId, cfsqltype="cf_sql_varchar");
			queryObj.addParam( name="teacherId", value=teacherId, cfsqltype="cf_sql_varchar" );
			queryObj.addParam( name="currentDateTime", value=currentDateTime, cfsqltype="cf_sql_char" );
	
			queryObj.execute( sql=sql );
			
		}
	}

	/**
	*
	* @hint "审核学生的素质选修课学分"
	* 
	* @templateId "模板"
	* @studentId "学号"
	* @courseRequiredCredit "审核学分要求"
	*/
	public void function auditCourseOptionalCredit( required string templateId, required string studentId, required numeric courseOptionalCredit ) output=true {

		transaction{

			var sequenceGraduateDetail = getsequenceGraduateDetail();
			/* 当前日期时间字符串 */
			var currentDateTime = getdatetimeAdvice().getCurrentDatetimeString();
			
			/* 当前审阅人 */
			var teacherId = getsessionAdvice().getAutherUserID();
			
			sequenceGraduateDetail.setStudentID(studentId);
			sequenceGraduateDetail.setGraduateCondition(templateId);
			var detailId = sequenceGraduateDetail.getID();
	
			var sql = "	SELECT
							p.credit_com
						FROM
							t_student_profile p
						WHERE
							p.stu_id = :studentId ";
	
			var queryObj = new Query( datasource=application.dnsMaster );
			queryObj.addParam( name="studentId", value=studentId, cfsqltype="cf_sql_varchar" );
			var rs_studentProfile = queryObj.execute( sql=sql ).getResult();
	
			var resultDesc = "";
			var passed = "0";
	
			if( rs_studentProfile.credit_com ge courseOptionalCredit){
				resultDesc = "该学生满足素质选修课总学分要求，学生当前素质选修课学分：" & rs_studentProfile.credit_com & ",审核要求学分：" & courseOptionalCredit;
				passed = "1";
			}else{
				resultDesc = "该学生不满足素质选修课总学分要求，学生当前素质选修课学分：" & rs_studentProfile.credit_com & ",审核要求学分：" & courseOptionalCredit;
				passed = "0";
			}
	
			sql = "	UPDATE
						t_grad_details g
					SET
						g.is_passed = :passed,
						g.result_descript = :resultDesc,
						g.tch_id = :teacherId,
						g.date_created = :currentDateTime 
					WHERE
						g.stu_gdr = :detailId ";
	
			queryObj = new Query( datasource=application.dnsMaster );
	
			queryObj.addParam( name="passed", value=passed, cfsqltype="cf_sql_char");
			queryObj.addParam( name="resultDesc", value=resultDesc, cfsqltype="cf_sql_varchar");
			queryObj.addParam( name="detailId", value=detailId, cfsqltype="cf_sql_varchar");
			queryObj.addParam( name="teacherId", value=teacherId, cfsqltype="cf_sql_varchar" );
			queryObj.addParam( name="currentDateTime", value=currentDateTime, cfsqltype="cf_sql_char" );
	
			queryObj.execute( sql=sql );
			
			
		}
	}


	/**
	*
	* @hint "审核学生必修课平均绩点"
	* 
	* @templateId "模板"
	* @studentId "学号"
	* @courseRequiredCreditPoint "绩点要求"
	*/
	public void function auditCourseRequiredAvgCreditPoint( required string templateId, required string studentId, required numeric courseRequiredCreditPoint ) output=true {
		
		transaction{
			
			var sequenceGraduateDetail = getsequenceGraduateDetail();
			/* 当前日期时间字符串 */
			var currentDateTime = getdatetimeAdvice().getCurrentDatetimeString();
			
			/* 当前审阅人 */
			var teacherId = getsessionAdvice().getAutherUserID();
			
			sequenceGraduateDetail.setStudentID(studentId);
			sequenceGraduateDetail.setGraduateCondition(templateId);
			var detailId = sequenceGraduateDetail.getID();
	
			var sql = "	SELECT
							p.credit_req_avgpt
						FROM
							t_student_profile p
						WHERE
							p.stu_id = :studentId ";
	
			var queryObj = new Query( datasource=application.dnsMaster );
			queryObj.addParam( name="studentId", value=studentId, cfsqltype="cf_sql_varchar" );
			var rs_studentProfile = queryObj.execute( sql=sql ).getResult();
	
			var resultDesc = "";
			var passed = "0";
	
			if( rs_studentProfile.credit_req_avgpt ge courseRequiredCreditPoint){
				resultDesc = "该学生满足必修课平均绩点要求，学生当前必修课平均绩点：" & rs_studentProfile.credit_req_avgpt & ",审核绩点要求：" & courseRequiredCreditPoint;
				passed = "1";
			}else{
				resultDesc = "该学生不满足必修课平均绩点要求，学生当前必修课平均绩点：" & rs_studentProfile.credit_req_avgpt & ",审核绩点要求：" & courseRequiredCreditPoint;
				passed = "0";
			}
	
			sql = "	UPDATE
						t_grad_details g
					SET
						g.is_passed = :passed,
						g.result_descript = :resultDesc,
						g.tch_id = :teacherId,
						g.date_created = :currentDateTime 
					WHERE
						g.stu_gdr = :detailId ";
	
			queryObj = new Query( datasource=application.dnsMaster );
	
			queryObj.addParam( name="passed", value=passed, cfsqltype="cf_sql_char");
			queryObj.addParam( name="resultDesc", value=resultDesc, cfsqltype="cf_sql_varchar");
			queryObj.addParam( name="detailId", value=detailId, cfsqltype="cf_sql_varchar");
			queryObj.addParam( name="teacherId", value=teacherId, cfsqltype="cf_sql_varchar" );
			queryObj.addParam( name="currentDateTime", value=currentDateTime, cfsqltype="cf_sql_char" );
	
			queryObj.execute( sql=sql );
			
		}
		
	}

	/**
	*
	* @hint "审核学生总学分平均绩点"
	* 
	* @templateId "模板"
	* @studentId "学号"
	* @courseRequiredCreditPoint "绩点要求"
	*/
	public void function auditCourseCreditPoint( required string templateId, required string studentId, required numeric courseCreditPoint ) output=true {
		
		transaction{
			
			var sequenceGraduateDetail = getsequenceGraduateDetail();
			/* 当前日期时间字符串 */
			var currentDateTime = getdatetimeAdvice().getCurrentDatetimeString();
			
			/* 当前审阅人 */
			var teacherId = getsessionAdvice().getAutherUserID();
			
			sequenceGraduateDetail.setStudentID(studentId);
			sequenceGraduateDetail.setGraduateCondition(templateId);
			var detailId = sequenceGraduateDetail.getID();
	
			var sql = "	SELECT
							p.credit_avgpt
						FROM
							t_student_profile p
						WHERE
							p.stu_id = :studentId ";
	
			var queryObj = new Query( datasource=application.dnsMaster );
			queryObj.addParam( name="studentId", value=studentId, cfsqltype="cf_sql_varchar" );
			var rs_studentProfile = queryObj.execute( sql=sql ).getResult();
	
			var resultDesc = "";
			var passed = "0";
	
			if( rs_studentProfile.credit_avgpt ge courseCreditPoint){
				resultDesc = "该学生满足平均绩点要求，学生当前平均绩点：" & rs_studentProfile.credit_avgpt & ",审核平均绩点要求：" & courseCreditPoint;
				passed = "1";
			}else{
				resultDesc = "该学生不满足平均绩点要求，学生当前平均绩点：" & rs_studentProfile.credit_avgpt & ",审核平均绩点要求：" & courseCreditPoint;
				passed = "0";
			}
	
			sql = "	UPDATE
						t_grad_details g
					SET
						g.is_passed = :passed,
						g.result_descript = :resultDesc,
						g.tch_id = :teacherId,
						g.date_created = :currentDateTime 
					WHERE
						g.stu_gdr = :detailId ";
	
			queryObj = new Query( datasource=application.dnsMaster );
	
			queryObj.addParam( name="passed", value=passed, cfsqltype="cf_sql_char");
			queryObj.addParam( name="resultDesc", value=resultDesc, cfsqltype="cf_sql_varchar");
			queryObj.addParam( name="detailId", value=detailId, cfsqltype="cf_sql_varchar");
			queryObj.addParam( name="teacherId", value=teacherId, cfsqltype="cf_sql_varchar" );
			queryObj.addParam( name="currentDateTime", value=currentDateTime, cfsqltype="cf_sql_char" );
	
			queryObj.execute( sql=sql );
		
		}
		
	}

}

</cfscript>